/**
 * Author: ZhangYang
 * Version: 1.0
 * 070905-070908: 基本完成，并测试通过（季报优化中用到）
 * 070910: 增加对validate-type配置为"Double"的处理, 并改进其它几种类型的判断
 * 070911: backupExcel()方法要实现为保存原始上传的EXCEL文件的形式（先去掉原扩展名，加上时间串，再加上.xls）；
 * 070912: 给用户提供将某一项数据所对应的单元格变红的接口，以便调用者在进行逻辑验证时对单元格做出错误提示；提供可以得到某项数据的实际行号、列号的方法；
 * 071016: 增加对min-length,max-value,min-value等配置的支持和相关验证；改进对max-length配置项的验证；
           增加对validate-type配置项为"Integer","Long","Short"的处理及相关验证；
 * 071017: 验证数据时，如果某个单元格存在公式或不支持的格式，则报错，以免漏掉数据；
           导入失败时，在上传的原始EXCEL中创建一个新Sheet，将所有出错提示信息写到该Sheet中；
           完善导入工具的一些细节验证逻辑，并将所有依赖的类都从Liferay框架中独立出来，以适应云南外围系统的使用环境；
 * 071018: 改进导入EXCEL时对所有单元格是否都已读到末尾的判断方法，避免遇到空白行就结束读取数据；
           更正对于valid-range为row+n、row-n、column+n、column-n等类型时单元格行列号的计算方法；
           更正读取数据的过程，避免遇到单元格为null时不进行任何验证；
 */
package com.pccw.portlet.util.excel.importutil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.pccw.portlet.util.DocumentOperationUtil;
import com.pccw.portlet.util.PccwCommonUtil;
import com.pccw.portlet.util.excel.Constants;
import com.pccw.portlet.util.excel.ExcelUtil;

public class ExcelImporter {
	private File templateFile;
	private File importFile;
	private HSSFWorkbook workbook;
	private HSSFCellStyle errorStyle;
	private List errorList = new ArrayList();
	private List blankDetailLineErrorList = new ArrayList(); // 用于临时缓存空白详细信息行的错误信息, added on 2007-11-12
	private ImportedExcelData importedExcelData = new ImportedExcelData(); //用于将导入的所有数据返回给调用者
	private Map headTitleRuleMap; //用于保存从模板中读取出来的头信息标题验证规则
	private Map detailTitleRuleMap; //用于保存从模板中读取出来的详细信息标题验证规则
	private Map headDataRuleMap; //用于保存从模板中读取出来的头信息数据验证规则
	private Map detailDataRuleMap; //用于保存从模板中读取出来的详细信息数据验证规则
	private TemplateValidateRule templateValidateRule = new TemplateValidateRule(); // 整个模板的验证规则全局配置
	private boolean existUnknownCellType = false; // 用于指示是否存在不支持的EXCEL单元格格式
	private boolean existStringCellWithNumberFormat = false; // 用于指示是否存在String类型数据的单元格设置成了数值格式的情况
	private String userId = ""; //操作用户的登录帐号，可以由页面传递进来
	private String importType = ""; // 导入类型，可以作为导入成功以后所保存的历史文件名的一部分
	private int currentSheetId = 0; // 当前正在读取的Sheet编号
	private int currentRowId = 0; // 当前正在读取的单元格所处的行号
	private short currentColumnId = 0; // 当前正在读取的单元格所处的列号

	/**
	 * Constructor（参数：templateFile, importFile）
	 */
	public ExcelImporter(File templateFile, File importFile) {
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(importFile);
			workbook = new HSSFWorkbook(fis);
			this.templateFile = templateFile;
			this.importFile = importFile;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (fis != null)
					fis.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Constructor（参数：templateFilePath, importFilePath）
	 */
	public ExcelImporter(String templateFilePath, String importFilePath) {
		File importFile = null;
		FileInputStream fis = null;
		try {
			importFile = new File(importFilePath);
			if(importFile != null){
				fis = new FileInputStream(importFile);
				// 由要导入的EXCEL文件对象生成一个workbook
				workbook = new HSSFWorkbook(fis);
				this.importFile = importFile;
				
				// 由给定的模板文件路径生成一个模板文件对象
				this.templateFile = new File(templateFilePath);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (fis != null)
					fis.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 执行导入EXCEL的过程，并将导入成功的数据保存到Map中，如果有错误，将提示信息保存到errorList中
	 */
	public boolean importExcel(){
		if(this.workbook == null){
			this.errorList.add(Constants.UNKNOWN_FORMAT_EXISTS_IN_UPLOAD_FILE);
			return false;
		}
		if(this.templateFile == null){
			this.errorList.add(Constants.TEMPLATE_FILE_ERROR_ON_SERVER_PLEASE_CONTACT_ADMIN);
			return false;
		}
		else{
			boolean ruleLoaded = this.loadValidateRules(); //
			
			// this.printRules(); // (*) 在控制台打印所有已加载的验证规则，测试用
			
			if(! ruleLoaded){
				this.errorList.add(Constants.TEMPLATE_FILE_ERROR_ON_SERVER_PLEASE_CONTACT_ADMIN);
				return false;
			}
		}
		
		// 设定错误提示的单元格格式
		this.errorStyle = this.workbook.createCellStyle();
		this.errorStyle = ExcelUtil.configErrorStyle(this.errorStyle, ExcelUtil.FGColor_Red);
		
		int sheetCountForImport = 1;
		// 如果模板中声明该EXCEL文件导入时为多表（sheet）导入，则循环导入各个sheet
		if(this.templateValidateRule.isMultiSheet()){
			sheetCountForImport = this.workbook.getNumberOfSheets();
		}
		
		boolean allSheetsImported = true; // 表示是否所有sheet的数据都导入成功的标志
		
		// 循环导入该EXCEL文件各个sheet的数据
		// 固定从第一个sheet开始导入，如果是多sheet导入，则循环导入所有的sheet
		// 以后改进时可以增加一个配置选项，用于指定要导入的sheet范围，可以指定几个sheet的编号或名称，或指定sheet编号的范围
		for(int i = 0; i < sheetCountForImport; i++ ){
			HSSFSheet sheet = this.workbook.getSheetAt(i);
			this.importedExcelData.addNameOfSheet(this.workbook.getSheetName(i)); // 把将要导入的Sheet名称保存到importedExcelData中
			this.currentSheetId = i;
			
			if(this.templateValidateRule.isValidateHeadTitleEnabled()){
				// 验证EXCEL的头信息标题，如果有错，说明上传的EXCEL文件与模板不符，立即返回
				if( ! validateTitle(this.headTitleRuleMap, sheet)){
					this.errorList.add(Constants.UPLOAD_FILE_NOT_ACCORD_WITH_TEMPLATE); 
					return false;
				}
			}
			
			if(this.templateValidateRule.isValidateDetailTitleEnabled()){
				// 验证EXCEL的详细信息标题，如果有错，说明上传的EXCEL文件与模板不符，立即返回
				if( ! validateTitle(this.detailTitleRuleMap, sheet)){
					this.errorList.add(Constants.UPLOAD_FILE_NOT_ACCORD_WITH_TEMPLATE); 
					return false;
				}
			}
			
			boolean sheetImported = this.readDataFromSheet(sheet);
			if(! sheetImported){
				allSheetsImported = false;
			}
		}
		
		if(allSheetsImported && this.errorList.size() == 0){
			return true;
		}
		else{
			return false;
		}
	}
	
	/**
	 * 从一个Sheet中导入数据，并将读取成功的数据存入importedExcelData对象中
	 */
	private boolean readDataFromSheet(HSSFSheet sheet){
		boolean result = true;
		if(this.readHeadDataFromSheet(sheet) == false){
			result = false;
		}
		
		if(readDetailDataFromSheet(sheet) == false){
			result = false;
		}
		
		return result;
	}
	
	/**
	 * 从一个Sheet中导入头信息数据，并将读取成功的数据存入importedExcelData对象中
	 */
	private boolean readHeadDataFromSheet(HSSFSheet sheet){
		if(sheet == null){
			return false;
		}
		
		boolean result = true;
		Map headDataMap = new HashMap();
		Object cellValue = null;
		
		if(this.headDataRuleMap == null || this.headDataRuleMap.size() == 0){
			return true;
		}
		
		Set ruleEntrySet = this.headDataRuleMap.entrySet();
		Iterator it = ruleEntrySet.iterator();
		
		while(it.hasNext()){
			Map.Entry ruleEntry = (Map.Entry)it.next();
			CellValidateRule rule = (CellValidateRule)ruleEntry.getValue();
			if(rule == null){
				continue;
			}
			
			this.currentRowId = rule.getRowId();
			this.currentColumnId = rule.getColumnId();
			
			HSSFRow row = sheet.getRow(rule.getRowId());
			if(row == null){
				continue;
			}
			
			HSSFCell cell = row.getCell(rule.getColumnId());
			
			// Added on 2007-10-18, begin
			if(cell == null){
				cell = row.createCell(rule.getColumnId());
			}
			cellValue = null;
			// Added on 2007-10-18, end
			
			if(this.templateValidateRule.isValidateDataEnabled()){
				if(this.validateCellValue(rule, cell) == true){
					cellValue = this.getCellValue(rule, cell);
				}
				else{
					result = false;
				}
			}
			else{
				cellValue = this.getCellValue(rule, cell);
			}
			
			headDataMap.put(rule.getCellName(), cellValue);
		}
		
		this.importedExcelData.addHeadDataOfSheet(headDataMap);
		
		return result;
	}
	
	/**
	 * 从一个Sheet中导入详细信息数据，并将读取成功的数据存入importedExcelData对象中
	 */
	private boolean readDetailDataFromSheet(HSSFSheet sheet){
		if(sheet == null){
			return false;
		}
		
		boolean result = true;
		int lineId = 0; // 记录当前所读的详细信息是第几行（列）
		boolean thisSheetHasDetailLine = false;
		boolean allLinesPassedValidation = true;
		List dataLinesOfSheet = new ArrayList();
		Object cellValue = null;
		
		// 循环读取各行详细信息的数据，直到读到该EXCEL文件的物理结束位置
		while( ! this.isAllDetailCellReachPhysicalEnd(sheet, lineId, this.detailDataRuleMap)){
			Map lineDataMap = new HashMap();
			boolean currentLinePassedValidation = true;

			if(this.isBlankDetailLine(sheet, lineId, this.detailDataRuleMap)){
				// 如果所要导入的详细信息行所有单元格都为空白，则根据导入验证的配置确定是否报错
				if( ! this.templateValidateRule.isBlankDetailLineAllowed()){
//					String errorMsg = "Excel文件中第" + (this.currentSheetId + 1) + "个Sheet\"" 
//						+ this.workbook.getSheetName(this.currentSheetId) + "\" 的第" + (lineId + 1) + "条详细信息未填写任何有效数据，请将数据填写完整或将该行（列）删除！";
					
					String errorMsg = "Excel\u6587\u4ef6\u4e2d\u7b2c" + (this.currentSheetId + 1) + "\u4e2aSheet\"" 
					+ this.workbook.getSheetName(this.currentSheetId) + "\" \u7684\u7b2c" + (lineId + 1) + "\u6761\u8be6\u7ec6\u4fe1\u606f\u672a\u586b\u5199\u4efb\u4f55\u6709\u6548\u6570\u636e\uff0c\u8bf7\u5c06\u6570\u636e\u586b\u5199\u5b8c\u6574\u6216\u5c06\u8be5\u884c\uff08\u5217\uff09\u5220\u9664\uff01";
					if(! this.blankDetailLineErrorList.contains(errorMsg)){
						this.blankDetailLineErrorList.add(errorMsg);
					}
				}
			}
			else{
				// 如果该行不是空白行，则将之前已经出现的空白行错误提示信息从缓存List中真正放入errorList中，防止某个EXCEL末尾有许多空行时也提示错误
				if(this.blankDetailLineErrorList != null && this.blankDetailLineErrorList.size() > 0){
					this.errorList.addAll(this.blankDetailLineErrorList);
					this.blankDetailLineErrorList.clear(); // 然后把空白行错误提示信息的缓存List清空
				}
				
				// 如果所要导入的行并非所有单元格全部为空白，则循环读取该行中各个detail-data-cell的值
				currentLinePassedValidation = true; // 初始化本行是否全部通过验证的标志
				thisSheetHasDetailLine = true;
				
				Set ruleEntrySet = this.detailDataRuleMap.entrySet();
				Iterator it = ruleEntrySet.iterator();
				while(it.hasNext()){
					Map.Entry ruleEntry = (Map.Entry)it.next();
					CellValidateRule rule = (CellValidateRule)ruleEntry.getValue();
					if(rule == null){
						//System.out.println("rule == null"); // (*)
						continue;
					}
					
					//System.out.println("reading data for " + rule.getCellName());
	
					// 根据该字段的验证规则、当前所导入的行号，来获得要读取的单元格对象
					HSSFCell cell = this.getDetailCellOfSheet(sheet, lineId, rule);
					cellValue = null; // Added on 2007-10-18
					
					if(this.templateValidateRule.isValidateDataEnabled()){
						if(this.validateCellValue(rule, cell) == true){
							cellValue = this.getCellValue(rule, cell);
						}
						else{
							result = false;
							currentLinePassedValidation = false;		
							//System.out.println("cell not passed validation!"); // (*)
						}
					}
					else{
						cellValue = this.getCellValue(rule, cell);
					}
					//======================add by liangcd===============================	
					if("String".equalsIgnoreCase(rule.getValidateType())){
						if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
							 if (HSSFDateUtil.isCellDateFormatted(cell)) {    
							        double d = cell.getNumericCellValue();    
							        Date date = HSSFDateUtil.getJavaDate(d);    
							        lineDataMap.put(rule.getCellName(), date);
							 } else {
								 lineDataMap.put(rule.getCellName(), SicenToComm(Double.parseDouble(cellValue.toString())));
							 }
						}else{
							lineDataMap.put(rule.getCellName(), cellValue);
						}
					}else{
						lineDataMap.put(rule.getCellName(), cellValue);
					}
					
					//System.out.println(rule.getCellName() + " = " + cellValue); //(*)
				}
				
				// 如果该行中所有单元格均通过验证，就保存数据
				//if(currentLinePassedValidation){ (*)
					dataLinesOfSheet.add(lineDataMap);
				//} (*)
			}
			
			lineId++;
		}
		
		if( ! allLinesPassedValidation){
			result = false;
		}
		
		if(this.templateValidateRule.isDetailMustExsit() && thisSheetHasDetailLine == false){
//			this.errorList.add("Excel文件中第" + (this.currentSheetId + 1) + "个Sheet\"" 
//					+ this.workbook.getSheetName(this.currentSheetId) + "\" 没有任何详细行信息！");
			this.errorList.add("Excel\u6587\u4ef6\u4e2d\u7b2c" + (this.currentSheetId + 1) + "\u4e2aSheet\"" 
					+ this.workbook.getSheetName(this.currentSheetId) + "\" \u6ca1\u6709\u4efb\u4f55\u8be6\u7ec6\u884c\u4fe1\u606f\uff01");
		}
		
		this.importedExcelData.addDetailDataOfSheet(dataLinesOfSheet);
		
		return result;
	}
	
	/**
	 * 得到该Sheet中目前将要读取的一组详细信息中某个单元格的行号
	 */
	private int getDetailCellRowId(int lineId, CellValidateRule rule){
		if(lineId < 0 || rule == null){
			return -1;
		}

		int rowId = rule.getRowId(); // 该字段的起始行号
		
		String validRange = rule.getValidRange();
		if(validRange == null || "".equals(validRange.trim())){
			rowId = -1;
		}
		else if("this".equals(validRange) && lineId > 0){
			rowId = -1;
		}
		else if("all".equals(validRange)){
			rowId = rowId + lineId;
		}
		else if("row+all".equals(validRange)){
			rowId = rowId + lineId;
		}
		else if("row-all".equals(validRange)){
			rowId = rowId - lineId;
		}
		else if(validRange.indexOf("row+") >= 0){
			int n = 0;
			
			try{
				n = Integer.parseInt(validRange.substring(validRange.indexOf("row+") + "row+".length()));
			}
			catch(Exception e){
			}
			
			if(lineId <= n){
				rowId = rowId + lineId;
			}
			else{
				rowId = -1;
			}
		}
		else if(validRange.indexOf("row-") >= 0){
			int n = 0;
			
			try{
				n = Integer.parseInt(validRange.substring(validRange.indexOf("row-") + "row-".length()));
			}
			catch(Exception e){
			}
			
			if(lineId <= n){
				rowId = rowId - lineId;
			}
			else{
				rowId = -1;
			}
		}
		
		// 判断是否超过了EXCEL允许的最大行号
		if(rowId > ExcelUtil.MAX_ROW_ID_IN_SHEET){
			rowId = -1;
		}
		
		return rowId;
	}
	
	/**
	 * 得到该Sheet中目前将要读取的一组详细信息中某个单元格的列号
	 */
	private short getDetailCellColumnId(int lineId, CellValidateRule rule){
		if(lineId < 0 || rule == null){
			return -1;
		}

		short columnId = rule.getColumnId();
		
		String validRange = rule.getValidRange();
		if(validRange == null || "".equals(validRange.trim())){
			columnId = -1;
		}
		else if("this".equals(validRange) && lineId > 0){
			columnId = -1;
		}
		else if("all".equals(validRange)){
			columnId = (short)(columnId + lineId);
		}
		else if("column+all".equals(validRange)){
			columnId = (short)(columnId + lineId);
		}
		else if("column-all".equals(validRange)){
			columnId = (short)(columnId - lineId);
		}
		else if(validRange.indexOf("column+") >= 0){
			int n = 0;
			
			try{
				n = Integer.parseInt(validRange.substring(validRange.indexOf("column+") + "column+".length()));
			}
			catch(Exception e){
			}
			
			if(lineId <= n){
				columnId = (short)(columnId + lineId);
			}
			else{
				columnId = -1;
			}
		}
		else if(validRange.indexOf("column-") >= 0){
			int n = 0;
			
			try{
				n = Integer.parseInt(validRange.substring(validRange.indexOf("column-") + "column-".length()));
			}
			catch(Exception e){
			}
			
			if(lineId <= n){
				columnId = (short)(columnId - lineId);
			}
			else{
				columnId = -1;
			}
		}
		
		// 判断是否超过了EXCEL允许的最大行号
		if(columnId > ExcelUtil.MAX_COLUMN_ID_IN_SHEET){
			columnId = -1;
		}
		
		return columnId;
	}
	
	/**
	 * 得到该Sheet中目前将要读取的一组详细信息中的某个单元格
	 */
	private HSSFCell getDetailCellOfSheet(HSSFSheet sheet, int lineId, CellValidateRule rule){
		if(sheet == null || lineId < 0 || rule == null){
			//System.out.println("Detail No." + lineId + " of " + rule.getCellName() + "is empty because sheet == null || lineId < 0 || rule == null."); // (*)
			return null;
		}

		int rowId = this.getDetailCellRowId(lineId, rule);
		short columnId = this.getDetailCellColumnId(lineId, rule);
		if(rowId < 0 || columnId < 0){
			//System.out.println("Detail No." + lineId + " of " + rule.getCellName() + "is empty because rowId < 0 || columnId < 0"); // (*)
			return null;
		}
		
		this.currentRowId = rowId;
		this.currentColumnId = columnId;

		HSSFRow row = sheet.getRow(rowId);
		HSSFCell cell = null;
		if(row != null){
			cell = row.getCell(columnId);
			if(cell == null){
				cell = row.createCell(columnId); // Added on 2007-10-18
			}
		}
		
		/*
		if(cell == null){
			System.out.println("getDetailCellOfSheet, rowId:" + rowId + " columnId:" + columnId + ", cell is null"); // (*)
		}
		else{
			System.out.println("getDetailCellOfSheet, rowId:" + rowId + " columnId:" + columnId + ", cell is not null"); // (*)
		}
		*/
		
		return cell;
	}
	
	/**
	 * 加载验证规则
	 * 从构造方法传递进来的模板文件中读取相关的验证规则数据
	 */
	private boolean loadValidateRules(){
		// 如果模板文件未指定，则无法加载验证规则
		if(this.templateFile == null){
			return false;
		}
		
		// 根据模板文件的扩展名来确定用何种规则加载实现类来读取验证规则
		String templateFileName = this.templateFile.getAbsolutePath();
		if(templateFileName == null || "".equals(templateFileName) || templateFileName.indexOf(".") == -1){
			return false;
		}
		
		ValidateRuleLoader ruleLoader = null; // 加载验证规则的工具类
		
		String fileExt = templateFileName.substring(templateFileName.lastIndexOf("."));
		if(".xls".equalsIgnoreCase(fileExt)){
			ruleLoader = new ValidateRuleExcelLoader();
		}
		else if(".xml".equalsIgnoreCase(fileExt)){
			ruleLoader = new ValidateRuleXmlLoader();
		}
		else{
			this.errorList.add(Constants.UNKNOWN_VALIDATE_RULE_TEMPLATE_FILE_FORMAT);
			return false;
		}
		
		boolean ruleLoaded = ruleLoader.loadValidateRules(this.templateFile);
		if( ! ruleLoaded){
			// 在控制台打印加载验证规则过程中所产生的错误提示信息
			List loadRuleErrors = ruleLoader.getErrorList();
			if(loadRuleErrors != null && loadRuleErrors.size() > 0){
				for(int i = 0; i < loadRuleErrors.size(); i++){
					System.out.println("error when loading validate rule: " + loadRuleErrors.get(i));
				}
			}
			return false;
		}

		// 1.读取整个模板的验证规则全局配置，封装成TemplateValidateRule对象
		this.templateValidateRule = ruleLoader.getTemplateValidateRule();
		
		// 2.读取头信息标题的验证规则
		this.headTitleRuleMap = ruleLoader.getHeadTitleRuleMap();
		
		// 3.读取详细信息标题的验证规则
		this.detailTitleRuleMap = ruleLoader.getDetailTitleRuleMap();
		
		// 4.读取头信息数据的验证规则
		this.headDataRuleMap = ruleLoader.getHeadDataRuleMap();
		
		// 5.读取详细信息数据的验证规则
		this.detailDataRuleMap = ruleLoader.getDetailDataRuleMap();
		
		return true;
	}
	
	/**
	 * 得到从EXCEL导入的数据，如果导入时有错误，则返回null
	 */
	public ImportedExcelData getImportedExcelData(){
		if(this.errorList == null || this.errorList.size() == 0){
			return this.importedExcelData;
		}
		else{
			return null;
		}
	}

	/**
	 * 验证要导入的EXCEL文件中的标题是否正确
	 */
	private boolean validateTitle(Map ruleMap, HSSFSheet sheet){
		if(sheet == null){
			return false;
		}
		
		if(ruleMap == null || ruleMap.size() == 0){
			return true;
		}
		
		boolean result = true;
		
		Set ruleEntrySet = ruleMap.entrySet();
		Iterator it = ruleEntrySet.iterator();
		while(it.hasNext()){
			Map.Entry ruleEntry = (Map.Entry)it.next();
			CellValidateRule rule = (CellValidateRule)ruleEntry.getValue();
			
			HSSFRow row = sheet.getRow(rule.getRowId());
			if(row == null){
				result = false;
				break;
			}

			HSSFCell cell = row.getCell(rule.getColumnId());
			if(cell == null){
				result = false;
				cell = row.createCell(rule.getColumnId());
				cell.setCellStyle(this.errorStyle);
				break;
			}
			else{
				String cellValueStr = ExcelUtil.getStringCellValue(cell);
				if(rule.getCellTitle() != null){
					if(cellValueStr == null || ! cellValueStr.equals(rule.getCellTitle())){
						result = false;
						cell.setCellStyle(this.errorStyle);
						break;
					}
				}
				else{
					if(cellValueStr != null && ! "".equals(cellValueStr)){
						result = false;
						cell.setCellStyle(this.errorStyle);
						break;
					}
				}
			}
		}
		
		return result;
	}
	
	/**
	 * 得到相关出错信息的List
	 */
	public List getErrorList(){
		return this.errorList;
	}
	
	/**
	 * 得到相关出错信息的String
	 * 即将errorList中的所有信息拼接起来（用下划线_分隔）
	 */
	public String getErrorString(){
		Iterator it = this.errorList.iterator();
		StringBuffer sb = new StringBuffer();
		
		while(it.hasNext()){
			String str = (String)it.next();
			sb.append(str);
			if(it.hasNext()){
				sb.append("_");
			}
		}
		
		return sb.toString();
	}
	
	/**
	 * 得到WorkBook
	 */
	public HSSFWorkbook getWorkbook() {
		return this.workbook;
	}
	
	/**
	 * 判断该EXCEL表中是否存在详细信息
	 */
	private static boolean isExistDetailRow_notFinished(Map detailRuleMap, HSSFSheet sheet){
		// (*)
		return true;
	}
	
	/**
	 * 判断该EXCEL表中某一行详细信息的所有列是否全部为空
	 */
	private boolean isBlankDetailRow_notFinished(HSSFRow row, Map detailRuleMap){
		// (*) 
		return true;
	}
	
	/**
	 * 判断该EXCEL表中某一列详细信息的所有行是否全部为空
	 */
	private boolean isBlankDetailColumn_notFinished(HSSFSheet sheet, short columnId, Map detailRuleMap){
		// (*) 
		return true;
	}
	
	/**
	 * 判断该EXCEL表中某一line详细信息的所有项是否全部为空
	 * 可以灵活适应行与列的详细信息单元格组合成一个line的方式
	 */
	private boolean isBlankDetailLine(HSSFSheet sheet, int lineId, Map detailRuleMap){
		if(sheet == null || lineId < 0 || detailRuleMap == null || detailRuleMap.size() == 0){
			return true;
		}
		
		boolean result = true;
		
		Set ruleEntrySet = detailRuleMap.entrySet();
		Iterator it = ruleEntrySet.iterator();
		while(it.hasNext()){
			Map.Entry ruleEntry = (Map.Entry)it.next();
			CellValidateRule rule = (CellValidateRule)ruleEntry.getValue();

			HSSFCell cell = this.getDetailCellOfSheet(sheet, lineId, rule);
			if(cell != null){
				String cellValueStr = ExcelUtil.getStringCellValue(cell);
				if(cellValueStr != null && ! "".equals(cellValueStr.trim())){
					//System.out.println("cell Value: " + cellValueStr + " ,so Line " + lineId + " is not a blank detail line.");
					result = false;
					break;
				}
			}
		}
		
		//if(result == true){
			//System.out.println("Line " + lineId + " is a blank detail line!");
		//}

		return result;
	}
	
	/**
	 * 判断目前是否已读到该EXCEL文件的物理结束位置，用于决定是否停止读取详细信息
	 * 可以灵活适应行与列的详细信息单元格组合成一个line的方式
	 */
	private boolean isAllDetailCellReachPhysicalEnd(HSSFSheet sheet, int lineId, Map detailRuleMap){
		if(sheet == null || lineId < 0 || detailRuleMap == null || detailRuleMap.size() == 0){
			return true;
		}
		
		boolean result = true;
		
		Set ruleEntrySet = detailRuleMap.entrySet();
		Iterator it = ruleEntrySet.iterator();
		while(it.hasNext()){
			Map.Entry ruleEntry = (Map.Entry)it.next();
			CellValidateRule rule = (CellValidateRule)ruleEntry.getValue();
			
			int rowId = this.getDetailCellRowId(lineId, rule);
			short columnId = this.getDetailCellColumnId(lineId, rule);
			
			//System.out.println("judge is Detail Cell Reach Physical End, rowId:" + rowId + " columnId:" + columnId); //(*)
			
			if(rowId >= 0 && rowId <= sheet.getLastRowNum() && columnId >= 0){
				HSSFRow row = sheet.getRow(rowId);
				if(row == null || columnId <= row.getLastCellNum()){
					result = false;
					//System.out.println("its not physical end because rowId < sheet.getLastRowNum() and columnId < row.getLastCellNum()"); // (*)
					break;
				}
			}
		}
		
		/*
		if(result == true){
			System.out.println("its the physical end");
		}
		*/

		return result;
	}
	
	/**
	 * 验证给定的规则对象所对应的单元格的内容
	 */
	private boolean validateCellValue(CellValidateRule rule, HSSFCell cell){
		boolean result = true;
		
		String cellValue = ExcelUtil.getStringCellValue(cell);
		
		StringBuffer sb = new StringBuffer(); // 提示信息中的sheet、行、列、字段名等信息
//		sb.append("Excel文件中第");
//		sb.append(this.currentSheetId + 1);
//		sb.append("个Sheet\"");
//		sb.append(this.workbook.getSheetName(this.currentSheetId));
//		sb.append("\" 第");
//		sb.append(this.currentRowId + 1);
//		sb.append("行 第");
//		sb.append(this.currentColumnId + 1);
//		sb.append("列的");
		sb.append("Excel\u6587\u4ef6\u4e2d\u7b2c");
		sb.append(this.currentSheetId + 1);
		sb.append("\u4e2aSheet\"");
		sb.append(this.workbook.getSheetName(this.currentSheetId));
		sb.append("\" \u7b2c");
		sb.append(this.currentRowId + 1);
		sb.append("\u884c \u7b2c");
		sb.append(this.currentColumnId + 1);
		sb.append("\u5217\u7684");

		String errorMsgHead = sb.toString();
		
		String detailErrorMsg = "";
		String batchErrorMsg = "";
		
		if(cell == null){
			if(rule.isRequired()){
				result = false;
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏不能为空！";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏不能为空！";
				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u4e0d\u80fd\u4e3a\u7a7a\uff01";
				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u4e0d\u80fd\u4e3a\u7a7a\uff01";
				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}

			return result; // cell为null，立即返回
		}
		
		// 判断单元格是否有公式
		if(ExcelUtil.isFormulaExistsInCell(cell)){
			result = false;
//			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏单元格含有公式，系统无法读取。请将公式删除，直接填写数据值！";
//			batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏单元格含有公式，系统无法读取。请将公式删除，直接填写数据值！";
			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5355\u5143\u683c\u542b\u6709\u516c\u5f0f\uff0c\u7cfb\u7edf\u65e0\u6cd5\u8bfb\u53d6\u3002\u8bf7\u5c06\u516c\u5f0f\u5220\u9664\uff0c\u76f4\u63a5\u586b\u5199\u6570\u636e\u503c\uff01";
			batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5355\u5143\u683c\u542b\u6709\u516c\u5f0f\uff0c\u7cfb\u7edf\u65e0\u6cd5\u8bfb\u53d6\u3002\u8bf7\u5c06\u516c\u5f0f\u5220\u9664\uff0c\u76f4\u63a5\u586b\u5199\u6570\u636e\u503c\uff01";
			this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			return false; // 单元格有公式，不必再进行其它验证，立即返回
		}
		// 判断单元格是否有系统不支持的格式
		else if( ! ExcelUtil.isCellTypeSupported(cell)){
			result = false;
//			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏单元格含有系统不支持的格式！";
//			batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏单元格含有系统不支持的格式！";
			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5355\u5143\u683c\u542b\u6709\u7cfb\u7edf\u4e0d\u652f\u6301\u7684\u683c\u5f0f\uff01";
			batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5355\u5143\u683c\u542b\u6709\u7cfb\u7edf\u4e0d\u652f\u6301\u7684\u683c\u5f0f\uff01";
			this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			return false; // 单元格有不支持的格式，不必再进行其它验证，立即返回
		}
		
		
		// 如果是必填项，验证是否为空
		if(rule.isRequired()){
			if(cellValue == null || "".equals(cellValue.trim())){
				result = false;
				//this.addComment(cell, "该栏不能为空！", rule.getRowId()); // Added on 2007-07-24，用于在单元格的批注中写入出错信息，但由于POI的功能问题，暂时无法实现
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏不能为空！";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏不能为空！";
				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u4e0d\u80fd\u4e3a\u7a7a\uff01";
				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u4e0d\u80fd\u4e3a\u7a7a\uff01";
				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}
		}
		
		double maxValue = rule.getMaxValue();
		double minValue = rule.getMinValue();
		String maxValueStr = String.valueOf(rule.getMaxValue());
		String minValueStr = String.valueOf(rule.getMinValue());
		boolean passedNumberTypesValidate = true; // 判断是否符合模板中指定的验证数据类型的要求
		
		// 如果规则要求是数字，验证是否为数字格式
		if("Number".equalsIgnoreCase(rule.getValidateType()) || "Double".equalsIgnoreCase(rule.getValidateType())){
			if(cellValue != null && ! "".equals(cellValue.trim()) && ! ExcelUtil.isNumber(cellValue)){
				result = false;
				//this.addComment(cell, "该栏必须输入数字！", rule.getRowId()); // Added on 2007-07-24，用于在单元格的批注中写入出错信息，但由于POI的功能问题，暂时无法实现
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏必须输入数字！";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏必须输入数字！";
				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6570\u5b57\uff01";
				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6570\u5b57\uff01";
				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}
		}
		// 如果规则要求是日期，验证是否为日期格式
		else if("Date".equalsIgnoreCase(rule.getValidateType())){
			if(cellValue != null && ! "".equals(cellValue.trim()) && ! ExcelUtil.isStandardDate(cellValue)){
				result = false;
				//this.addComment(cell, "该栏所输入的日期不存在或格式不正确（标准格式为：2007-03-05)", rule.getRowId()); // Added on 2007-07-24，用于在单元格的批注中写入出错信息，但由于POI的功能问题，暂时无法实现
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏所输入的日期不存在或格式不正确（标准格式为：2007-03-05)！";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏所输入的日期不存在或格式不正确（标准格式为：2007-03-05)！";
				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u6240\u8f93\u5165\u7684\u65e5\u671f\u4e0d\u5b58\u5728\u6216\u683c\u5f0f\u4e0d\u6b63\u786e\uff08\u6807\u51c6\u683c\u5f0f\u4e3a\uff1a2007-03-05)\uff01";
				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u6240\u8f93\u5165\u7684\u65e5\u671f\u4e0d\u5b58\u5728\u6216\u683c\u5f0f\u4e0d\u6b63\u786e\uff08\u6807\u51c6\u683c\u5f0f\u4e3a\uff1a2007-03-05)\uff01";
				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}
		}
		// 如果规则要求是字符串，验证单元格是否为文本格式
		else if("String".equalsIgnoreCase(rule.getValidateType())){
			if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
				//===========================edit by liangcd====================================
				//result = false;
//				this.existStringCellWithNumberFormat = true;
//				//this.addComment(cell, "该单元格应该为文本格式，却设置为了数值格式，可能导致数据导入出错！", rule.getRowId()); // Added on 2007-07-24，用于在单元格的批注中写入出错信息，但由于POI的功能问题，暂时无法实现
////				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏的单元格应该为文本格式，却设置为了数值格式，可能导致数据导入出错！";
////				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏的单元格应该为文本格式，却设置为了数值格式，可能导致数据导入出错！";
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u5355\u5143\u683c\u5e94\u8be5\u4e3a\u6587\u672c\u683c\u5f0f\uff0c\u5374\u8bbe\u7f6e\u4e3a\u4e86\u6570\u503c\u683c\u5f0f\uff0c\u53ef\u80fd\u5bfc\u81f4\u6570\u636e\u5bfc\u5165\u51fa\u9519\uff01";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u5355\u5143\u683c\u5e94\u8be5\u4e3a\u6587\u672c\u683c\u5f0f\uff0c\u5374\u8bbe\u7f6e\u4e3a\u4e86\u6570\u503c\u683c\u5f0f\uff0c\u53ef\u80fd\u5bfc\u81f4\u6570\u636e\u5bfc\u5165\u51fa\u9519\uff01";
//				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}
		}
		// 如果规则要求是Integer类型，验证数据格式是否正确
		else if("Integer".equalsIgnoreCase(rule.getValidateType())){
			if(maxValue > Integer.MAX_VALUE){
				maxValue = Integer.MAX_VALUE;
				maxValueStr = String.valueOf(Integer.MAX_VALUE);
			}
			if(minValue < Integer.MIN_VALUE || minValue == Double.MIN_VALUE){
				minValue = Integer.MIN_VALUE;
				minValueStr = String.valueOf(Integer.MIN_VALUE);
			}
			
			if(cellValue != null && ! "".equals(cellValue.trim())){
				Double doubleObject = PccwCommonUtil.getDoubleObjectFromString(cellValue);
				if(doubleObject == null){ // 说明输入的不是数字
					passedNumberTypesValidate = false;
				}
				else if(doubleObject.doubleValue() <= Integer.MAX_VALUE && doubleObject.doubleValue() >= Integer.MIN_VALUE){
					Integer integerObject = PccwCommonUtil.getIntegerObjectFromString(cellValue);
					if(integerObject == null){
						passedNumberTypesValidate = false;
					}
				}
			}
			
			if(passedNumberTypesValidate == false){
				result = false;
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏必须输入整数！";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏必须输入整数！";
				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6574\u6570\uff01";
				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6574\u6570\uff01";
				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}
		}
		// 如果规则要求是Long类型，验证数据格式是否正确
		else if("Long".equalsIgnoreCase(rule.getValidateType())){
			if(maxValue > Long.MAX_VALUE){
				maxValue = Long.MAX_VALUE;
				maxValueStr = String.valueOf(Long.MAX_VALUE);
			}
			if(minValue < Long.MIN_VALUE || minValue == Double.MIN_VALUE){
				minValue = Long.MIN_VALUE;
				minValueStr = String.valueOf(Long.MIN_VALUE);
			}
			
			if(cellValue != null && ! "".equals(cellValue.trim())){
				Double doubleObject = PccwCommonUtil.getDoubleObjectFromString(cellValue);
				if(doubleObject == null){
					passedNumberTypesValidate = false;
				}
				else if(doubleObject.doubleValue() <= Long.MAX_VALUE && doubleObject.doubleValue() >= Long.MIN_VALUE){
					Long longObject = PccwCommonUtil.getLongObjectFromString(cellValue);
					if(longObject == null){
						passedNumberTypesValidate = false;
					}
				}
			}
			
			if(passedNumberTypesValidate == false){
				result = false;
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏必须输入整数！";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏必须输入整数！";
				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6574\u6570\uff01";
				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6574\u6570\uff01";
				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}
		}
		// 如果规则要求是Short类型，验证数据格式是否正确
		else if("Short".equalsIgnoreCase(rule.getValidateType())){
			if(maxValue > Short.MAX_VALUE){
				maxValue = Short.MAX_VALUE;
				maxValueStr = String.valueOf(Short.MAX_VALUE);
			}
			if(minValue < Short.MIN_VALUE || minValue == Double.MIN_VALUE){
				minValue = Short.MIN_VALUE;
				minValueStr = String.valueOf(Short.MIN_VALUE);
			}
			
			if(cellValue != null && ! "".equals(cellValue.trim())){
				Double doubleObject = PccwCommonUtil.getDoubleObjectFromString(cellValue);
				if(doubleObject == null){
					passedNumberTypesValidate = false;
				}
				else if(doubleObject.doubleValue() <= Short.MAX_VALUE && doubleObject.doubleValue() >= Short.MIN_VALUE){
					Short shortObject = PccwCommonUtil.getShortObjectFromString(cellValue);
					if(shortObject == null){
						passedNumberTypesValidate = false;
						
					}
				}
			}
			
			if(passedNumberTypesValidate == false){
				result = false;
//				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏必须输入整数！";
//				batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏必须输入整数！";
				detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6574\u6570\uff01";
				batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u5fc5\u987b\u8f93\u5165\u6574\u6570\uff01";
				this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
			}
		}
		
		
		// 如果规则要求是各种数值类型，验证是否符合最大值与最小值要求
		if("Number".equalsIgnoreCase(rule.getValidateType()) || "Double".equalsIgnoreCase(rule.getValidateType()) 
				|| "Integer".equalsIgnoreCase(rule.getValidateType()) || "Long".equalsIgnoreCase(rule.getValidateType())
				|| "Short".equalsIgnoreCase(rule.getValidateType())){
			
			if(passedNumberTypesValidate && cellValue != null && ! "".equals(cellValue)){
				Double doubleObject = PccwCommonUtil.getDoubleObjectFromString(cellValue);
				if(doubleObject != null && doubleObject.doubleValue() > maxValue){
					result = false;
					if(maxValueStr != null && maxValueStr.endsWith(".0")){
						maxValueStr = maxValueStr.substring(0, maxValueStr.indexOf(".0"));
					}
//					detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏的值不能大于" + maxValueStr + "！";
//					batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏的值不能大于" + maxValueStr + "！";
					detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u503c\u4e0d\u80fd\u5927\u4e8e" + maxValueStr + "\uff01";
					batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u503c\u4e0d\u80fd\u5927\u4e8e" + maxValueStr + "\uff01";
					this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
				}
				
				if(minValue != Double.MIN_VALUE && doubleObject != null && doubleObject.doubleValue() < minValue){
					result = false;
					if(minValueStr != null && minValueStr.endsWith(".0")){
						minValueStr = minValueStr.substring(0, minValueStr.indexOf(".0"));
					}
//					detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏的值不能小于" + minValueStr + "！";
//					batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏的值不能小于" + minValueStr + "！";
					detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u503c\u4e0d\u80fd\u5c0f\u4e8e" + minValueStr + "\uff01";
					batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u503c\u4e0d\u80fd\u5c0f\u4e8e" + minValueStr + "\uff01";
					this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
				}
			}
		}
		
		// 如果有最大长度限制，验证长度是否超过限制, -999代表没有长度限制
		int maxLength = rule.getMaxLength();
		if(maxLength != -999 && ExcelUtil.isExceedCellLengthLimit(cell, maxLength)){
			result = false;
			//this.addComment(cell, "该栏的长度不能超过" + limit + "个字符（或" + limit/3 + "个汉字）！", rule.getRowId()); // Added on 2007-07-24，用于在单元格的批注中写入出错信息，但由于POI的功能问题，暂时无法实现
			String chineseLimitMsg = "";
			if(maxLength >= 3){
//				chineseLimitMsg = "（或" + maxLength/3 + "个汉字）";
				chineseLimitMsg = "\uff08\u6216" + maxLength/3 + "\u4e2a\u6c49\u5b57\uff09";
			}
//			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏的长度不能超过" + maxLength + "个字符" + chineseLimitMsg + "！";
//			batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏的长度不能超过" + maxLength + "个字符" + chineseLimitMsg + "！";
			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u957f\u5ea6\u4e0d\u80fd\u8d85\u8fc7" + maxLength + "\u4e2a\u5b57\u7b26" + chineseLimitMsg + "\uff01";
			batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u957f\u5ea6\u4e0d\u80fd\u8d85\u8fc7" + maxLength + "\u4e2a\u5b57\u7b26" + chineseLimitMsg + "\uff01";
			this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
		}
		
		// 如果有最小长度要求，验证长度是否小于最小长度
		int minLength = rule.getMinLength();
		if(minLength > 0 && ExcelUtil.isLessThanCellLengthRequirement(cell, minLength)){
			result = false;
			//this.addComment(cell, "该栏的长度不能少于" + limit + "个字符（或" + limit/3 + "个汉字）！", rule.getRowId()); // Added on 2007-07-24，用于在单元格的批注中写入出错信息，但由于POI的功能问题，暂时无法实现
			String chineseLimitMsg = "";
			//if(minLength >= 3){
//				chineseLimitMsg = "（或" + ((minLength + 2)/3) + "个汉字）";
			chineseLimitMsg = "\uff08\u6216" + ((minLength + 2)/3) + "\u4e2a\u6c49\u5b57\uff09";
			//}
//			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"一栏的长度不能少于" + minLength + "个字符" + chineseLimitMsg + "！";
//			batchErrorMsg = "\"" + rule.getCellTitle() + "\"一栏的长度不能少于" + minLength + "个字符" + chineseLimitMsg + "！";
			detailErrorMsg = errorMsgHead + "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u957f\u5ea6\u4e0d\u80fd\u5c11\u4e8e" + minLength + "\u4e2a\u5b57\u7b26" + chineseLimitMsg + "\uff01";
			batchErrorMsg = "\"" + rule.getCellTitle() + "\"\u4e00\u680f\u7684\u957f\u5ea6\u4e0d\u80fd\u5c11\u4e8e" + minLength + "\u4e2a\u5b57\u7b26" + chineseLimitMsg + "\uff01";
			this.addCellErrorMessage(rule, batchErrorMsg, detailErrorMsg);
		}
		
		if(result == false){
			cell.setCellStyle(this.errorStyle);
		}
		
		return result;
	}
	
	/**
	 * 往errorList中添加出错提示信息
	 */
	private void addCellErrorMessage(CellValidateRule rule, String batchErrorMsg, String detailErrorMsg){
		if(rule.hasBatchErrorMsgInErrorList() && ! this.errorList.contains(batchErrorMsg)){
			this.errorList.add(batchErrorMsg);
		}
		if(rule.hasDetailErrorMsgInErrorList()){
			this.errorList.add(detailErrorMsg);
		}
	}
	
	/**
	 * 读取给定的规则对象所对应的单元格的内容
	 * 根据其cellValidateRule中的配置返回相应类型的对象
	 */
	private Object getCellValue(CellValidateRule rule, HSSFCell cell){
		String cellValueString = ExcelUtil.getStringCellValue(cell);
		String returnType = rule.getReturnType();
		
		if(returnType == null || "".equals(returnType) || "String".equalsIgnoreCase(returnType)){
			return cellValueString;
		}
		else if("Double".equalsIgnoreCase(returnType)){
			return PccwCommonUtil.getDoubleObjectFromString(cellValueString);
		}
		else if("Integer".equalsIgnoreCase(returnType)){
			return PccwCommonUtil.getIntegerObjectFromString(cellValueString);
		}
		else if("Long".equalsIgnoreCase(returnType)){
			return PccwCommonUtil.getLongObjectFromString(cellValueString);
		}
		else if("Short".equalsIgnoreCase(returnType)){
			return PccwCommonUtil.getShortObjectFromString(cellValueString);
		}
		else if("Date".equalsIgnoreCase(returnType)){
			return PccwCommonUtil.getJavaUtilDateFromDateString(cellValueString);
		}
		else{
			return cellValueString;
		}
	}
	
	/**
	 * 将验证过程中的出错提示信息写入单元格的批注中
	 * Added on 2007-07-24，但由于POI的功能问题，暂时无法实现
	 */
	/*
	private String addComment(HSSFCell cell, String newComment, int rowId){
		if(cell == null || this.patriarch == null){
			return null;
		}

		HSSFComment comment = cell.getCellComment();
		HSSFRichTextString commentStr = null;
		String commentContent = "";

		if(comment == null){
			HSSFClientAnchor anchor = new HSSFClientAnchor();
			anchor.setRow1(rowId);
			if(newComment.length() > 100){
				anchor.setRow2(rowId + newComment.length()/20 + 1);
			}
			else{
				anchor.setRow2(rowId + 5);
			}
			anchor.setCol1((short)(cell.getCellNum() + 1));
			anchor.setCol2((short)(cell.getCellNum() + 6));
			comment = patriarch.createComment(anchor);
			commentContent = newComment;
		}
		else{
			commentStr = comment.getString();
			if(commentStr == null){
				commentContent = newComment;
			}
			else{
				commentContent = commentStr.getString() + " " + newComment;
			}
		}
		
		commentStr = new HSSFRichTextString(commentContent);
		if(this.commentFont == null){
			this.commentFont = this.workbook.createFont();
			this.commentFont.setColor(HSSFColor.RED.index);
		}
		commentStr.applyFont(this.commentFont);
		comment.setString(commentStr);
		cell.setCellComment(comment);
		
		return commentContent;
	}
	*/
	
	/**
	 * 得到带有错误提示信息的EXCEL文件
	 * 并给this.workbook创建一个新Sheet，将this.errorList中的所有出错提示信息写到该Sheet中(2007-10-17实现)
	 * 有时间再实现
	 */
	public File getErrorFile() {
		String directory = null;
		FileOutputStream fos = null;
		File outFile = null;
		
		// Edited on 2007-06-12（如果用户上传的EXCEL文件格式无法识别，比如有自动筛选，为避免出错，把供下载的错误文件由修改后的原EXCEL文件改为一个固定的文本文件）
		if(this.workbook == null){
			outFile = DocumentOperationUtil.getTempTxtFile(Constants.UNKNOWN_FORMAT_EXISTS_IN_UPLOAD_FILE);
		}
		else{
			try {
				// 将所有出错提示信息写入一个新的Sheet中, Added on 2007-10-17
				if(this.errorList != null && this.errorList.size() > 0){
					HSSFSheet sheet = null;
					try{
//						sheet = this.workbook.createSheet("错误信息");
						sheet = this.workbook.createSheet("\u9519\u8bef\u4fe1\u606f");
					}
					catch(Exception e){
//						sheet = this.workbook.createSheet("错误信息" + System.currentTimeMillis());
						sheet = this.workbook.createSheet("\u9519\u8bef\u4fe1\u606f" + System.currentTimeMillis());
					}
					
					HSSFCellStyle style = this.workbook.createCellStyle();
					style.setWrapText(true); // 设置为自动换行
					
					for(int i = 0; i < this.errorList.size(); i++){
						String error = (String)this.errorList.get(i);
						HSSFRow row = sheet.createRow(i);
						sheet.setColumnWidth((short)0, ExcelUtil.COMMON_MAX_COLUMN_WIDTH); // 设置该列的宽度
						HSSFCell cell = row.createCell((short)0);
						cell.setCellStyle(style);
						cell.setCellValue(new HSSFRichTextString(error));
					}
				}
				
				directory = DocumentOperationUtil.createTempDir();
				File deleteDir = new File(directory);
				String[] fileNames = deleteDir.list();
				for (int i = 0; fileNames != null && i < fileNames.length; i++) {
					if ("error.xls".equals(fileNames[i])) {
						boolean deleted = new File(deleteDir.getAbsolutePath() + "/"
								+ fileNames[i]).delete();
					}
				}
				if((!directory.endsWith("/")) && (!directory.endsWith(File.separator))){
					directory = directory + "/";
				}
				outFile = new File(directory + "error.xls");
				fos = new FileOutputStream(outFile);
				this.workbook.write(fos);
				fos.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				try {
					if (fos != null)
						fos.close();
				} catch (IOException e) {
				}
			}
		}
		
		return outFile;
	}
	
	/**
	 * 将指定的头信息数据的单元格设置为红色，以提示导入者该项数据有错误
	 * 在逻辑验证发现问题时调用
	 * 注意：sheetId值从0开始
	 */
	public boolean setErrorStyleInExcelForHeadData(int sheetId, String cellName){
		if(sheetId < 0 || cellName == null || "".equals(cellName)){
			return false;
		}
		
		CellValidateRule rule = (CellValidateRule)this.headDataRuleMap.get(cellName);
		if(rule == null){
			return false;
		}
		
		HSSFSheet sheet = this.workbook.getSheetAt(sheetId);
		if(sheet == null){
			return false;
		}

		int rowId = rule.getRowId();
		short columnId = rule.getColumnId();
		
		if(rowId < 0 || columnId < 0){
			return false;
		}
		
		HSSFRow row = sheet.getRow(rowId);
		if(row == null){
			row = sheet.createRow(rowId);
		}
		
		HSSFCell cell = row.getCell(columnId);
		if(cell == null){
			cell = row.createCell(columnId);
		}
		
		cell.setCellStyle(this.errorStyle);
		
		return true;
	}
	
	/**
	 * 将指定的详细信息数据的单元格设置为红色，以提示导入者该项数据有错误
	 * 在逻辑验证发现问题时调用
	 * 注意：sheetId、lineId值从0开始
	 */
	public boolean setErrorStyleInExcelForDetailData(int sheetId, int lineId, String cellName){
		if(sheetId < 0 || lineId < 0 || cellName == null || "".equals(cellName)){
			return false;
		}
		
		CellValidateRule rule = (CellValidateRule)this.detailDataRuleMap.get(cellName);
		if(rule == null){
			return false;
		}
		
		HSSFSheet sheet = this.workbook.getSheetAt(sheetId);
		if(sheet == null){
			return false;
		}

		int rowId = this.getDetailCellRowId(lineId, rule);
		short columnId = this.getDetailCellColumnId(lineId, rule);
		
		if(rowId < 0 || columnId < 0){
			return false;
		}
		
		HSSFRow row = sheet.getRow(rowId);
		if(row == null){
			row = sheet.createRow(rowId);
		}
		
		HSSFCell cell = row.getCell(columnId);
		if(cell == null){
			cell = row.createCell(columnId);
		}
		
		cell.setCellStyle(this.errorStyle);
		
		return true;
	}
	
	/**
	 * 得到某个头信息单元格在EXCEL文件中所处的行号（注意：值从0开始）
	 */
	public int getExcelRowIdOfHeadData(String cellName){
		if(cellName == null || "".equals(cellName)){
			return -1;
		}
		
		CellValidateRule rule = (CellValidateRule)this.headDataRuleMap.get(cellName);
		if(rule == null){
			return -1;
		}
		else{
			return rule.getRowId();
		}
	}
	
	/**
	 * 得到某个详细信息单元格在EXCEL文件中所处的行号（注意：值从0开始）
	 */
	public int getExcelRowIdOfDetailData(int lineId, String cellName){
		if(cellName == null || "".equals(cellName)){
			return -1;
		}
		
		CellValidateRule rule = (CellValidateRule)this.detailDataRuleMap.get(cellName);
		if(rule == null){
			return -1;
		}
		else{
			return this.getDetailCellRowId(lineId, rule);
		}
	}
	
	/**
	 * 得到某个头信息单元格在EXCEL文件中所处的列号（注意：值从0开始）
	 */
	public short getExcelColumnIdOfHeadData(String cellName){
		if(cellName == null || "".equals(cellName)){
			return -1;
		}
		
		CellValidateRule rule = (CellValidateRule)this.headDataRuleMap.get(cellName);
		if(rule == null){
			return -1;
		}
		else{
			return rule.getColumnId();
		}
	}
	
	/**
	 * 得到某个详细信息单元格在EXCEL文件中所处的列号（注意：值从0开始）
	 */
	public short getExcelColumnIdOfDetailData(int lineId, String cellName){
		if(cellName == null || "".equals(cellName)){
			return -1;
		}
		
		CellValidateRule rule = (CellValidateRule)this.detailDataRuleMap.get(cellName);
		if(rule == null){
			return -1;
		}
		else{
			return this.getDetailCellColumnId(lineId, rule);
		}
	}
	
	/**
	 * 将导入成功的EXCEL文件从内存中的Workbook对象保存到历史目录中，以备需要时查看
	 * 可以让用户指定文件名（包括保存路径），然后自动把年-月-日-时-分-秒的字符串加到文件名末尾（扩展名前面）
	 */
	public File backupWorkbookToExcel(String filePath) {
		// 根据给定的文件全路径（包括文件名），在扩展名前面追加“年_月_日_时_分_秒”的字符串
		filePath = this.getFilePathWithDateTimePostfix(filePath); 
		if(filePath == null || "".equals(filePath.trim()) || this.workbook == null){
			return null;
		}
		
		FileOutputStream fos = null;
		File outFile = null;

		try {
			outFile = new File(filePath);
			fos = new FileOutputStream(outFile);
			this.workbook.write(fos);
			fos.close();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (fos != null)
					fos.close();
			} catch (IOException e) {
			}
		}
		
		return outFile;
	}
	
	/**
	 * 将导入成功的原始EXCEL文件保存到历史目录中，以备需要时查看
	 * 可以让用户指定文件名（包括保存路径），然后自动把年-月-日-时-分-秒的字符串加到文件名末尾（扩展名前面）
	 */
	public File backupExcel(String filePath){
		if(this.importFile == null || ! this.importFile.exists()){
			return null;
		}
		
		// 根据给定的文件全路径（包括文件名），在扩展名前面追加“年_月_日_时_分_秒”的字符串
		filePath = this.getFilePathWithDateTimePostfix(filePath); 
		if(filePath == null || "".equals(filePath.trim()) || this.workbook == null){
			return null;
		}
		
		FileOutputStream fos = null;
		FileInputStream fis=null;
		File outFile = null;
		try{
			outFile = new File(filePath);
			if(! outFile.exists()){
				outFile.createNewFile();
			}
			
			fos = new FileOutputStream(outFile);
			fis = new FileInputStream(this.importFile);
			byte[] content=new byte[1024];
			int t = 0;
			while((t = fis.read(content)) <= 1024 && t != -1){
				fos.write(content, 0, t);
			}
		}
		catch(Exception e){
			System.out.println("============================================");
			System.out.println("In ExcelImporter, backupExcel Failed! file name: " + filePath);
			System.out.println("============================================");
		}
		finally{
			try {
				if (fos != null && fis!=null){
					fos.close();
					fis.close();
				}
			} catch (IOException e) {
			}
		}

		return outFile;
	}
	
	/**
	 * 根据给定的文件全路径（包括文件名），在扩展名前面追加“年_月_日_时_分_秒”的字符串
	 */
	private String getFilePathWithDateTimePostfix(String filePath){
		if(filePath == null || "".equals(filePath.trim())){
			return null;
		}

		// 去掉原文件名的扩展名
		int indexOfExtension = filePath.lastIndexOf(".");
		
		String filePathWithoutExtension = filePath;
		if(indexOfExtension > 0){
			filePathWithoutExtension = filePath.substring(0, indexOfExtension);
		}
		
		Calendar cal = Calendar.getInstance();
		cal.setTimeInMillis(System.currentTimeMillis());
		String year = cal.get(Calendar.YEAR) + "";
		String month = (cal.get(Calendar.MONTH) + 1) + "";
		if(month.length() == 1){
			month = "0" + month;
		}
		
		String day = (cal.get(Calendar.DAY_OF_MONTH)) + "";
		if(day.length() == 1){
			day = "0" + day;
		}
		
		String hour = (cal.get(Calendar.HOUR_OF_DAY)) + "";
		if(hour.length() == 1){
			hour = "0" + hour;
		}
		
		String min = (cal.get(Calendar.MINUTE)) + "";
		if(min.length() == 1){
			min = "0" + min;
		}
		
		String second = (cal.get(Calendar.SECOND)) + "";
		if(second.length() == 1){
			second = "0" + second;
		}
		
		StringBuffer fileName = new StringBuffer();
		fileName.append(filePathWithoutExtension);
		fileName.append("_");
		if(this.importType != null && ! "".equals(this.importType)){
			fileName.append(this.importType);
			fileName.append("_");
		}
		fileName.append(year);
		fileName.append(month);
		fileName.append(day);
		fileName.append("_");
		fileName.append(hour);
		fileName.append(min);
		fileName.append(second);
		fileName.append(".xls");

		return fileName.toString();
	}
	
	public String getImportType() {
		return importType;
	}

	public void setImportType(String importType) {
		this.importType = importType;
	}

	public File getTemplateFile() {
		return templateFile;
	}

	public String getUserId() {
		return userId;
	}

	public void setUserId(String userId) {
		this.userId = userId;
	}
	
	/**
	 * 在控制台打印所有已加载的验证规则，测试用
	 */
	private void printRules(){
		this.templateValidateRule.print();
		this.printRulesInMap(this.headTitleRuleMap);
		this.printRulesInMap(this.headDataRuleMap);
		this.printRulesInMap(this.detailTitleRuleMap);
		this.printRulesInMap(this.detailDataRuleMap);
	}
	
	/**
	 * 在控制台打印Map中的所有验证规则，测试用
	 */
	private void printRulesInMap(Map ruleMap){
		Set entrySet = ruleMap.entrySet();
		Iterator it = entrySet.iterator();
		while(it.hasNext()){
			Map.Entry entry = (Map.Entry)it.next();
			CellValidateRule rule = (CellValidateRule)entry.getValue();
			rule.print();
		}
	}
	
	/*
	 * 转换科学计算法
	 */
	 public String SicenToComm(double value) {  
        String retValue = null;   
        DecimalFormat df = new DecimalFormat();   
        //df.setMinimumFractionDigits(1);   
       // df.setMaximumFractionDigits(1);   
        retValue = df.format(value); 
        retValue = retValue.replaceAll(",","");  
        return retValue;   
	 }  
}
